﻿
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Collections;
    using System.Collections.Generic;

namespace FShop_DAL{

    ///<summary>
    ///The Class Provides methods for common database table operations. 
    ///</summary>
    ///<remarks>
    ///Changes to this file may cause incorrect behavior and will be lost if  the code is regenerated.
    ///Update the <see cref="User_DAL"/> class if you need to add or change some functionality
    ///</remarks>
    public class User_DAL:FShop.DAL.BaseObject  {

        private const string SP_SELECT_BY_PRIMARY_KEY="usp_User_SelectByPrimaryKey";
        private const string SP_SELECT_ROWS_BY_WHERE="usp_User_SelectRowsByWhere";
        private const string SP_INSERT="usp_User_Insert";
        private const string SP_UPDATE="usp_User_Update";
        private const string SP_DELETE="usp_User_DeleteByPrimaryKey";
        private const string SP_DELETE_WHERE="usp_User_DeleteWhere";
        private const string SP_SELECT_GENERAL="usp_User_SelectGeneral";
        private const string SP_SELECT_PAGING="usp_User_SelectPaging";

        #region Constructor

        ///<summary>
        ///Initializes a new instance of the <see cref="UserDal"/> 
        ///class with the specified <see cref="BaseDAL"/>.
        ///</summary>
        public User_DAL(): base()
        {

        }

        #endregion

        #region Public Methods
        ///<summary>
        ///Create Obj from ireader.
        ///</summary>

        private User GetObjectFromReader(IDataReader reader) 
         {
             User temp= new User();
             temp.User_ID = (reader["User_ID"] is DBNull) ? int.MinValue :(System.Int32)reader["User_ID"] ;
             temp.User_Name = (reader["User_Name"] is DBNull) ? string.Empty :(System.String)reader["User_Name"] ;
             temp.User_Email = (reader["User_Email"] is DBNull) ? string.Empty :(System.String)reader["User_Email"] ;
             temp.User_Address = (reader["User_Address"] is DBNull) ? string.Empty :(System.String)reader["User_Address"] ;
             temp.User_Password = (reader["User_Password"] is DBNull) ? string.Empty :(System.String)reader["User_Password"] ;
             temp.User_Sex = (reader["User_Sex"] is DBNull) ? false :(System.Boolean)reader["User_Sex"] ;
             temp.User_Phone = (reader["User_Phone"] is DBNull) ? string.Empty :(System.String)reader["User_Phone"] ;
             temp.User_Company = (reader["User_Company"] is DBNull) ? string.Empty :(System.String)reader["User_Company"] ;
             temp.User_Status = (reader["User_Status"] is DBNull) ? false :(System.Boolean)reader["User_Status"] ;
             temp.User_Type = (reader["User_Type"] is DBNull) ? int.MinValue :(System.Int32)reader["User_Type"] ;
             temp.User_IsAdmin = (reader["User_IsAdmin"] is DBNull) ? false :(System.Boolean)reader["User_IsAdmin"] ;
             temp.User_Nick = (reader["User_Nick"] is DBNull) ? string.Empty :(System.String)reader["User_Nick"] ;
              return temp;
         }
        ///<summary>
        ///Get List User by a primary key in object 
        ///</summary>
		public User Get_User_ByID(System.Int32 User_ID)
		{
		    SqlConnection conn = null;
		    try
		        {
		            using( conn = CreateConnection() )
		            {
                         SqlCommand cmd = new SqlCommand(SP_SELECT_BY_PRIMARY_KEY, conn);
                         cmd.CommandType = CommandType.StoredProcedure;
				        cmd.Parameters.Add(new SqlParameter("@User_ID",User_ID));
                         conn.Open();
                         User temp = new User();
                         using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection) )
                         {
                             while (reader.Read())
                                 {
                                     temp=( GetObjectFromReader(reader) );
                                 }
                         }
                     conn.Close();
                     return temp;
		            }
		        }
                catch
		        {
                     return null;
		        }
                finally
		        {
		            if(conn.State==ConnectionState.Open)
                         conn.Close();
		        }
		 }
        public User Get_User_ByName(String User_Name)
        {
            SqlConnection conn = null;
            try
            {
                using (conn = CreateConnection())
                {
                    SqlCommand cmd = new SqlCommand(SP_SELECT_GENERAL, conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@User_Name", User_Name));
                    conn.Open();
                    User temp = new User();
                    using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (reader.Read())
                        {
                            temp = (GetObjectFromReader(reader));
                        }
                    }
                    conn.Close();
                    return temp;
                }
            }
            catch
            {
                return null;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
        }
        ///<summary>
        ///Get List User and store in a list 
        ///</summary>
		public List<User> GetList_User()
		{
		    using( SqlConnection conn = CreateConnection() )
		    {
                SqlCommand cmd = new SqlCommand(SP_SELECT_ROWS_BY_WHERE, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@SqlWhere",""));
                conn.Open();
                List<User> lst = new List<User>();
                using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection) )
                {
                    while (reader.Read())
                     {
                         lst.Add( GetObjectFromReader(reader) );
                     }
                }
                conn.Close();
                return lst;
		    }
		 }

		public void Delete_User(User temp)
		{
		    using( SqlConnection conn = CreateConnection() )
		    {
                SqlCommand cmd = new SqlCommand(SP_DELETE, conn);
                cmd.CommandType = CommandType.StoredProcedure;
				cmd.Parameters.Add(new SqlParameter("@User_ID",temp.User_ID));
                try
		        {
                 conn.Open();
                 cmd.ExecuteNonQuery();
                 conn.Close();
		        }
                catch(SqlException ex)
		        {
		            throw ex;
		        }
                finally
		        {
		            if(conn.State==ConnectionState.Open)
                         conn.Close();
		        }
		    }
		 }

		public void Delete_User_Where(string whereSql)
		{
		    using( SqlConnection conn = CreateConnection() )
		    {
                SqlCommand cmd = new SqlCommand(SP_DELETE_WHERE, conn);
                cmd.CommandType = CommandType.StoredProcedure;
			    cmd.Parameters.Add(new SqlParameter("@Where",whereSql));
                try
		        {
                 conn.Open();
                 cmd.ExecuteNonQuery();
                 conn.Close();
		        }
                catch(SqlException ex)
		        {
		            throw ex;
		        }
                finally
		        {
		            if(conn.State==ConnectionState.Open)
                         conn.Close();
		        }
		    }
		 }

        ///<summary>
        ///Get List User as DataTable 
        ///</summary>
        /// <param name="whereSql">Where fields. example : 1=1 </param>
        /// <param name="orderBy"Order fields></param>
		public DataTable Get_UserAsTable(string whereSql,string orderBy)
		{
		    using( SqlConnection conn = CreateConnection() )
		    {
                SqlCommand cmd = new SqlCommand(SP_SELECT_GENERAL, conn);
                cmd.CommandType = CommandType.StoredProcedure;
			    cmd.Parameters.Add(new SqlParameter("@SqlWhere",whereSql));
			    cmd.Parameters.Add(new SqlParameter("@SqlOrderBy",orderBy));
                SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
                try
		       {
                 conn.Open();
                 DataTable tbl = new DataTable();
                 dataAdapter.Fill(tbl);
                 conn.Close();
                 return tbl;
		       }
                catch
		        {
                     return null;
		        }
                finally
		        {
		            if(conn.State==ConnectionState.Open)
                         conn.Close();
		        }
		    }
		 }

        ///<summary>
        ///Using for paging data 
        ///</summary>
        /// <param name="pageIndex">Number of pages</param>
        /// <param name="PageSize">Number of a rows per page</param>
        /// <param name="conditions">Where  fields</param>
        /// <param name="groupBy">Group by fields</param>
		public DataTable Get_UserAsPaging(int pageIndex,int pageSize,string sSortColumn,string sFields,string sWhere,string sGroupBy)
		{
		    using( SqlConnection conn = CreateConnection() )
		    {
                SqlCommand cmd = new SqlCommand(SP_SELECT_PAGING, conn);
                cmd.CommandType = CommandType.StoredProcedure;
			    cmd.Parameters.Add(new SqlParameter("@PageIndex",pageIndex));
			    cmd.Parameters.Add(new SqlParameter("@PageSize",pageSize));
			    cmd.Parameters.Add(new SqlParameter("@SortColumn",sSortColumn));
			    cmd.Parameters.Add(new SqlParameter("@Fields",sFields));
			    cmd.Parameters.Add(new SqlParameter("@Where",sWhere));
			    cmd.Parameters.Add(new SqlParameter("@Group",sGroupBy));
                SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
                try
		       {
                 conn.Open();
                 DataTable tbl = new DataTable();
                 dataAdapter.Fill(tbl);
                 conn.Close();
                 return tbl;
		       }
                catch
		        {
                     return null;
		        }
                finally
		        {
		            if(conn.State==ConnectionState.Open)
                         conn.Close();
		        }
		    }
		 }

        ///<summary>
        ///Insert new User
        ///</summary>
		public void Insert_User(User temp)
		{
		    using( SqlConnection conn = CreateConnection() )
		    {
                SqlCommand cmd = new SqlCommand(SP_INSERT, conn);
                cmd.CommandType = CommandType.StoredProcedure;
				cmd.Parameters.Add(new SqlParameter("@User_Name",temp.User_Name));
				cmd.Parameters.Add(new SqlParameter("@User_Email",temp.User_Email));
				cmd.Parameters.Add(new SqlParameter("@User_Address",temp.User_Address));
				cmd.Parameters.Add(new SqlParameter("@User_Password",temp.User_Password));
				cmd.Parameters.Add(new SqlParameter("@User_Sex",temp.User_Sex));
				cmd.Parameters.Add(new SqlParameter("@User_Phone",temp.User_Phone));
				cmd.Parameters.Add(new SqlParameter("@User_Company",temp.User_Company));
				cmd.Parameters.Add(new SqlParameter("@User_Status",temp.User_Status));
				cmd.Parameters.Add(new SqlParameter("@User_Type",temp.User_Type));
				cmd.Parameters.Add(new SqlParameter("@User_IsAdmin",temp.User_IsAdmin));
				cmd.Parameters.Add(new SqlParameter("@User_Nick",temp.User_Nick));
                try
		        {
                 conn.Open();
                 cmd.ExecuteNonQuery();
                 conn.Close();
		        }
                catch(SqlException ex)
		        {
		            throw ex;
		        }
                finally
		        {
		            if(conn.State==ConnectionState.Open)
                         conn.Close();
		        }
		    }
		 }

        ///<summary>
        ///Update a User
        ///</summary>
		public void Update_User(User temp)
		{
		    using( SqlConnection conn = CreateConnection() )
		    {
                SqlCommand cmd = new SqlCommand(SP_UPDATE, conn);
                cmd.CommandType = CommandType.StoredProcedure;
				cmd.Parameters.Add(new SqlParameter("@User_ID",temp.User_ID));
				cmd.Parameters.Add(new SqlParameter("@User_Name",temp.User_Name));
				cmd.Parameters.Add(new SqlParameter("@User_Email",temp.User_Email));
				cmd.Parameters.Add(new SqlParameter("@User_Address",temp.User_Address));
				cmd.Parameters.Add(new SqlParameter("@User_Password",temp.User_Password));
				cmd.Parameters.Add(new SqlParameter("@User_Sex",temp.User_Sex));
				cmd.Parameters.Add(new SqlParameter("@User_Phone",temp.User_Phone));
				cmd.Parameters.Add(new SqlParameter("@User_Company",temp.User_Company));
				cmd.Parameters.Add(new SqlParameter("@User_Status",temp.User_Status));
				cmd.Parameters.Add(new SqlParameter("@User_Type",temp.User_Type));
				cmd.Parameters.Add(new SqlParameter("@User_IsAdmin",temp.User_IsAdmin));
				cmd.Parameters.Add(new SqlParameter("@User_Nick",temp.User_Nick));
                try
		        {
                 conn.Open();
                 cmd.ExecuteNonQuery();
                 conn.Close();
		        }
                catch(SqlException ex)
		        {
		            throw ex;
		        }
                finally
		        {
		            if(conn.State==ConnectionState.Open)
                         conn.Close();
		        }
		    }
		 }

#endregion
 }
}
